{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 准备工作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 247,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "中山大学南方学院\n",
    "中山大学南方学院团委\n",
    "中山大学南方学院图书馆\n",
    "中大南方财务部\n",
    "中大南方大学英语教学中心\n",
    "中大南方学工部\n",
    "中大南方学院网络一站式服务中心\n",
    "中大南方就业指导中心\n",
    "中大南方教务部\n",
    "中大南方普通话测试站\n",
    "中大南方物流服务中心\n",
    "中大南方社团联合会\n",
    "中大南方学生会\n",
    "\n",
    "'''\n",
    "\n",
    "公众号 = \"中山大学南方学院\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 采集公众号（requests）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "metadata": {},
   "outputs": [],
   "source": [
    "fn = { \"output\" : { \"中山大学南方学院_htm_snippets\": \"data_raw_src/公众号_htm_snippets_中山大学南方学院.tsv\",\n",
    "                    \"中山大学南方学院_df\": \"data_raw_src/公众号_df_中山大学南方学院.tsv\",\n",
    "                    \"中山大学南方学院_xlsx\": \"data_sets/公众号_url_中山大学南方学院.xlsx\",\n",
    "                    \"中山大学南方学院_xlsx\": \"data_sets/公众号_全部推文_中山大学南方学院.xlsx\"，\n",
    "                    \"中山大学南方学院_xlsx\": \"data_sets/公众号_学生相关_中山大学南方学院.xlsx\"} \\\n",
    "      }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    },
    {
     "ename": "KeyError",
     "evalue": "'app_msg_list'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-221-a44d4d7d9405>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     40\u001b[0m     \u001b[1;31m#print(content_json)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     41\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 42\u001b[1;33m     \u001b[1;32mfor\u001b[0m \u001b[0mitem\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mcontent_json\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"app_msg_list\"\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     43\u001b[0m     \u001b[1;31m# 提取每页文章的标题及对应的url\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     44\u001b[0m         \u001b[0mitems\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: 'app_msg_list'"
     ]
    }
   ],
   "source": [
    "# 目标url\n",
    "\n",
    "import time\n",
    "import requests\n",
    "import pandas as pd\n",
    "import csv\n",
    "\n",
    "#这是微信公众号登陆界面\n",
    "url = \"https://mp.weixin.qq.com/cgi-bin/appmsg\"\n",
    "\n",
    "\n",
    "# 使用Cookie，跳过登陆操作\n",
    "headers = {\n",
    "  \"Cookie\": \"ua_id=qkHYa993RdupTfbnAAAAAH3TDCBztpVWmzJWaRhKH0s=; mm_lang=zh_CN; pgv_pvi=5659016192; RK=YeRVbnt0F1; ptcz=ad6552e7b9f4b1642ae0d51d23987d52ff700454817c6774fd02b7ccb17afb08; pgv_pvid=4665696405; o_cookie=1047338697; pac_uid=1_1047338697; wxuin=68184591681596; _ga=GA1.2.amp-FqcMXjNBQihWP1Wn_xfRXA; ptui_loginuin=leijiayingabc@qq.com; noticeLoginFlag=1; pgv_si=s4279587840; uuid=4c8065daf1522b57371b7abae893816c; ticket=0b3656d49a684da1f904b66765cce4948819c4f1; ticket_id=gh_8c176a439ca2; cert=SoyEZTvUVSSWLDxzBA2p17bU16VbxTLD; rand_info=CAESIGCDFzWsrYxv64Ox2VmiM2aVKzZV2finH2x0FLlcid/P; slave_bizuin=3097152003; data_bizuin=3072055504; bizuin=3097152003; data_ticket=Zln8WWlrcOz7E7smA/oJa956Eur04KAH/E3p+UurmUeGhUlz4iB/LtqAp9CprvxT; slave_sid=UlRCNldhNWxlcUV4VWpxZ2Q3dVZRVGdzMUpfWjFwRHRTdjJwYTU3aHpoeDZtcVNBU09fNEZOa3JpV0xDV3A1bnY3SDBNcFVSMU1jeGxXWXJsY0lNODdHVHNKWU81S3VaUjFJeTNSOXJNMjdoaWhVTXI2Y05KQUJCU1pBS2tUeFdIaVM2TUJpN1E5S3pONDBF; slave_user=gh_8c176a439ca2; xid=36e46c04aae5f0a93995e5d26f380152; openid2ticket_o0Ngbt8dy9EzwIOg2cCkwOAp9Yus=jcdukP8azv/FBVCh0NwwvX+u3FWlg5NxGXF/ySVwCG4=; rewardsn=; wxtokenkey=777\",\n",
    "  \"user-agent\": \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.138 Safari/537.36\"\n",
    "}\n",
    "\n",
    "data = {\n",
    "    \"action\": \"list_ex\",\n",
    "    \"begin\":\"0\",\n",
    "    \"count\": \"5\",\n",
    "    \"fakeid\": \"MzAxNTEwNjI1Ng==\",\n",
    "    \"type\": \"9\",\n",
    "    \"token\":\"249742979\",\n",
    "    \"query\":\"\",\n",
    "    \"lang\": \"zh_CN\",\n",
    "    \"f\": \"json\",\n",
    "    \"ajax\":\"1\",\n",
    "}\n",
    "\n",
    "\n",
    "content_list=[]\n",
    "\n",
    "for i in range(5):\n",
    "    data[\"begin\"] = i*5\n",
    "    print(data[\"begin\"])\n",
    "    time.sleep(3)\n",
    "    # 使用get方法进行提交\n",
    "    content_json = requests.get(url, headers=headers, params=data).json()\n",
    "    #print(content_json)\n",
    "    \n",
    "    for item in content_json[\"app_msg_list\"]:\n",
    "    # 提取每页文章的标题及对应的url\n",
    "        items = []\n",
    "        items.append(item[\"title\"])\n",
    "        items.append(item[\"link\"])\n",
    "        items.append(item[\"create_time\"])\n",
    "        content_list.append(items)\n",
    "\n",
    "\n",
    "name=['title','link','create_time']\n",
    "test=pd.DataFrame(columns=name,data=content_list)\n",
    "with pd.ExcelWriter(fn[\"output\"][\"公众号_xlsx\"].format(公众号=\"中山大学南方学院_requests\")) as writer:\n",
    "    test.to_excel(writer)\n",
    "\n",
    "#test.to_csv(\"../微信公众号爬虫_雷嘉盈/中山大学南方学院.csv\",mode='a',encoding='utf-8')\n",
    "# print(\"保存成功\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 采集公众号（selenium）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from lxml.html import fromstring\n",
    "import time\n",
    "from random import random"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 使用Selenium\n",
    "* 要更改 opts.binary_location 至自己本地的Chrome浏览器，建议portable\n",
    "* Chrome浏览器 和 chromedriver.exe要同版本号到小数后一位\n",
    "* 要确保可以 开启浏览器机器人\n",
    "* 要确保浏览器机器人 可以打开网页 driver.get(\"https://mp.weixin.qq.com\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\EmmaLui\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:18: DeprecationWarning: use options instead of chrome_options\n"
     ]
    }
   ],
   "source": [
    "from selenium import webdriver\n",
    "from selenium.webdriver.common.desired_capabilities import DesiredCapabilities\n",
    "\n",
    "#caps=dict()\n",
    "#caps[\"pageLoadStrategy\"] = \"none\"   # Do not wait for full page load\n",
    "\n",
    "opts = webdriver.ChromeOptions()\n",
    "opts.add_argument('--no-sandbox')#解决DevToolsActivePort文件不存在的报错\n",
    "opts.add_argument('window-size=1920x3000') #指定浏览器分辨率\n",
    "opts.add_argument('--disable-gpu') #谷歌文档提到需要加上一这个属性来规避bug\n",
    "opts.add_argument('--hide-scrollbars') #隐藏滚动条, 应对些特殊页面\n",
    "#opts.add_argument('blink-settings=imagesEnabled=false') #不加载图片, 提升速度\n",
    "#opts.add_argument('--headless') #浏览器不提供可视化页面. linux下如果系统不支持可视化不加这条会启动失败\n",
    "\n",
    "opts.binary_location = r\"C:\\Users\\EmmaLui\\AppData\\Local\\Google\\Chrome\\Application\\chrome.exe\" #\"H:\\_coding_\\Gitee\\InternetNewMedia\\CapstonePrj2016\\chromedriver.exe\"  \n",
    "\n",
    "# \"H:\\_coding_\\Gitee\\InternetNewMedia\\CapstonePrj2016\\chromedriver.exe\"  \n",
    "driver = webdriver.Chrome( chrome_options = opts) #desired_capabilities=caps,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.get(\"https://mp.weixin.qq.com\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 填表登入"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "selenium 的定位方法\n",
    "* find_element_by_id &ensp;&ensp;&ensp;  根据标签id定位\n",
    "* find_element_by_name   &ensp;&ensp;&ensp; 根据标签的name定位\n",
    "* find_element_by_xpath  &ensp;&ensp;&ensp; 根据xpath定位\n",
    "* find_element_by_link_text  &ensp;&ensp;&ensp; 通过文字链接来定位元素\n",
    "* find_element_by_partial_link_text  &ensp;&ensp;&ensp;  通过文字链接来定位元素\n",
    "* find_element_by_tag_name  &ensp;&ensp;&ensp;  根据标签的名字定位\n",
    "* find_element_by_class_name  &ensp;&ensp;&ensp; 通过class name 定位\n",
    "* find_element_by_css_selector  &ensp;&ensp;&ensp;  根据元素属性来定位"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "#使用账号登陆\n",
    "driver.find_element_by_xpath('//div[@class=\"login__type__container login__type__container__scan\"]/a').click()\n",
    "payload =  {\"account\": \"494780741@qq.com\", \"password\": \"EmmaLui0118\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "#输入账号密码\n",
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"account\"]').clear()\n",
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"account\"]').send_keys(payload['account'])\n",
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"password\"]').clear()\n",
    "driver.find_element_by_xpath('//form[@class=\"login_form\"]//input[@name=\"password\"]').send_keys(payload['password'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [],
   "source": [
    "#点击登录\n",
    "driver.find_element_by_xpath('//div[@class=\"login_btn_panel\"]/a').click()\n",
    "#然后手机扫码"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 点选单"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "其他常用方法\n",
    "* size：返回元素的尺寸\n",
    "* text：获取元素的文本\n",
    "* get_attribute：获取属性值  &ensp;&ensp;&ensp; get_attribute('innerHTML')获取元素内的全部HTML\n",
    "* is_displayed()：设置该元素用户是否可见"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'展开'"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//a[@id=\"m_open\"]')\n",
    "element.click()\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "main_content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.execute_script(\"window.scrollTo(0,document.body.scrollHeight)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'https://mp.weixin.qq.com/cgi-bin/appmsg?begin=0&count=10&t=media/appmsg_list&type=10&action=list&token=1575469406&lang=zh_CN'"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//li[@title[contains(.,\"素材管理\")]]/a') \n",
    "# main_content = element.get_attribute('innerHTML')\n",
    "# main_content\n",
    "url_素材管理= element.get_attribute(\"href\")\n",
    "url_素材管理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "#用driver.get的方式访问上面的链接：url_素材管理\n",
    "driver.get(url_素材管理)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 新建图文消息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "element = driver.find_element_by_xpath('//*[@id=\"js_main\"]/div[3]/div[1]/div[2]/button') \n",
    "main_content = element.get_attribute('innerHTML')\n",
    "main_content\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 新建图文消息开了另一分视窗，所以要切换 switch_to \n",
    "driver.switch_to.window(driver.window_handles[-1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 超链接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                超链接              \n"
     ]
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//*[@id=\"js_editor_insertlink\"]') \n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "选择其他公众号\n"
     ]
    }
   ],
   "source": [
    "# 点 选择其他公众号\n",
    "element = driver.find_element_by_xpath('//*[@id=\"vue_app\"]/div[5]/div[1]/div/div[2]/div[2]/form[1]/div[3]/div/div/p/button') \n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.find_element_by_xpath('//form//div[@class=\"inner_link_account_area\"]//input[@class=\"weui-desktop-form__input\"]').clear()\n",
    "driver.find_element_by_xpath('//form//div[@class=\"inner_link_account_area\"]//input[@class=\"weui-desktop-form__input\"]').send_keys(公众号)\n",
    "#输入第一行块代码的公众号"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<div class=\"weui-desktop-icon weui-desktop-icon__inputSearch weui-desktop-icon__small\"><!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <!----> <svg width=\"16\" height=\"16\" viewBox=\"0 0 16 16\" xmlns=\"http://www.w3.org/2000/svg\"><path d=\"M11.33 10.007l4.273 4.273a.502.502 0 0 1 .005.709l-.585.584a.499.499 0 0 1-.709-.004L10.046 11.3a6.278 6.278 0 1 1 1.284-1.294zm.012-3.729a5.063 5.063 0 1 0-10.127 0 5.063 5.063 0 0 0 10.127 0z\"></path></svg> <!----> <!----> <!----> <!----></div>\n"
     ]
    }
   ],
   "source": [
    "# 点放大镜搜\n",
    "element = driver.find_element_by_xpath('//*[@id=\"vue_app\"]/div[5]/div[1]/div/div[2]/div[2]/form[1]/div[3]/div/div/div/div/span/span/button[2]')\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/gwtgyqQSKdWE48e6H0JK4GgQDRibtiarEaJwM2aYdkXribv4pxXqjV5COaPtQRShXo6aGog6OpJTux354j641SpYA/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">中山大学南方学院</strong> <i class=\"inner_link_account_wechat\">微信号：zdnfgfwx</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/t6LDc48K10yk0l7bgqkSuZp9hKzia2syn3ibzE3xFX9vOYupTIVuY1Uic7V2ZFziacq6Xz5UX9fcuDK3BvXDKToEMQ/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">中山大学南方学院招生办公室</strong> <i class=\"inner_link_account_wechat\">微信号：NFU_ZSB</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/xn2T4wz2HZUxL2S52gBCxIcd9bA7SLXSAjqcNPe50VNAQ7pPiaKFnxBYm7wl7Yic4AwPViaFqicLOMjBFzxqVxEybQ/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">中山大学南方学院团委</strong> <i class=\"inner_link_account_wechat\">微信号：NFSYSUTW</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/4olDia1NV6Lgtx8I49jUW2I5XvtyKtBnc2zggP2nCPRZGbKf6PndlcVaCu5wSH9a2gsKSl9r3xq0kFCuMibFwicAg/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">中山大学南方学院继教院</strong> <i class=\"inner_link_account_wechat\">微信号：zdnfjjy</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">服务号</div></li><li class=\"inner_link_account_item\"><div class=\"weui-desktop-vm_primary\"><img src=\"http://mmbiz.qpic.cn/mmbiz_png/nHT5x7sbpibd9nbU6ibgibdiacwNklVPaHIibwyjpFNuiaM6AGyCvZ3NhkJPzoWchoPqpETVgblupM7cGrJfibvqYFeJA/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">中山大学南方学院继续教育学院</strong> <i class=\"inner_link_account_wechat\">微信号：未设置</i></div> <div class=\"weui-desktop-vm_default inner_link_account_type\">订阅号</div></li>\n"
     ]
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//ul[@class=\"inner_link_account_list\"]')\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "公众号SERP = main_content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 解析\n",
    "root = fromstring(公众号SERP) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [],
   "source": [
    "主 = root.xpath('//li[@class=\"inner_link_account_item\"]')\n",
    "\n",
    "account_list = []\n",
    "for e in 主:\n",
    "    account_nickname = e.xpath('./div/strong[@class=\"inner_link_account_nickname\"]')[0].text\n",
    "    account_wechat = e.xpath('./div/i[@class=\"inner_link_account_wechat\"]')[0].text\n",
    "    account_img = e.xpath('./div/img/@src')[0]\n",
    "    account = {\"nickname\": account_nickname, \"wechat\": account_wechat, \"img\": account_img,}\n",
    "    account_list.append(account)\n",
    "\n",
    "df_account = pd.DataFrame(account_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nickname</th>\n",
       "      <th>wechat</th>\n",
       "      <th>img</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>中山大学南方学院</td>\n",
       "      <td>微信号：zdnfgfwx</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/gwtgyqQSKdWE48e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中山大学南方学院招生办公室</td>\n",
       "      <td>微信号：NFU_ZSB</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/t6LDc48K10yk0l7...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>中山大学南方学院团委</td>\n",
       "      <td>微信号：NFSYSUTW</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/xn2T4wz2HZUxL2S...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>中山大学南方学院继教院</td>\n",
       "      <td>微信号：zdnfjjy</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/4olDia1NV6Lgtx8...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>中山大学南方学院继续教育学院</td>\n",
       "      <td>微信号：未设置</td>\n",
       "      <td>http://mmbiz.qpic.cn/mmbiz_png/nHT5x7sbpibd9nb...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         nickname        wechat  \\\n",
       "0        中山大学南方学院  微信号：zdnfgfwx   \n",
       "1   中山大学南方学院招生办公室   微信号：NFU_ZSB   \n",
       "2      中山大学南方学院团委  微信号：NFSYSUTW   \n",
       "3     中山大学南方学院继教院   微信号：zdnfjjy   \n",
       "4  中山大学南方学院继续教育学院       微信号：未设置   \n",
       "\n",
       "                                                 img  \n",
       "0  http://mmbiz.qpic.cn/mmbiz_png/gwtgyqQSKdWE48e...  \n",
       "1  http://mmbiz.qpic.cn/mmbiz_png/t6LDc48K10yk0l7...  \n",
       "2  http://mmbiz.qpic.cn/mmbiz_png/xn2T4wz2HZUxL2S...  \n",
       "3  http://mmbiz.qpic.cn/mmbiz_png/4olDia1NV6Lgtx8...  \n",
       "4  http://mmbiz.qpic.cn/mmbiz_png/nHT5x7sbpibd9nb...  "
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_account"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<img src=\"http://mmbiz.qpic.cn/mmbiz_png/gwtgyqQSKdWE48e6H0JK4GgQDRibtiarEaJwM2aYdkXribv4pxXqjV5COaPtQRShXo6aGog6OpJTux354j641SpYA/0?wx_fmt=png\" class=\"inner_link_account_avatar\"> <strong class=\"inner_link_account_nickname\">中山大学南方学院</strong> <i class=\"inner_link_account_wechat\">微信号：zdnfgfwx</i>\n"
     ]
    }
   ],
   "source": [
    "element = driver.find_element_by_xpath('//*[@id=\"vue_app\"]/div[5]/div[1]/div/div[2]/div[2]/form[1]/div[3]/div/div/div/div[2]/ul/li[1]/div[1]')\n",
    "main_content = element.get_attribute('innerHTML')\n",
    "print(main_content)\n",
    "element.click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\n跳转_input = driver.find_element_by_xpath(\\'//span[@class=\"weui-desktop-pagination__form\"]/input\\')\\n跳转_a = driver.find_element_by_xpath(\\'//span[@class=\"weui-desktop-pagination__form\"]/a\\')\\n跳转_input.clear()\\n跳转_input.send_keys(2)\\n跳转_a.click()\\n'"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 跳转testing\n",
    "'''\n",
    "跳转_input = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/input')\n",
    "跳转_a = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/a')\n",
    "跳转_input.clear()\n",
    "跳转_input.send_keys(2)\n",
    "跳转_a.click()\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1, 199]\n",
      "False\n"
     ]
    }
   ],
   "source": [
    "# 跳转上限\n",
    "l_e = driver.find_elements_by_xpath('//label[@class=\"weui-desktop-pagination__num\"]')\n",
    "l_e_int  = [int(x.text) for x in l_e] \n",
    "print (l_e_int)\n",
    "print (l_e_int[0]==l_e_int[-1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 199]\n"
     ]
    }
   ],
   "source": [
    "pages = list(range(l_e_int[0],l_e_int[-1]+1 ))\n",
    "#print(pages[0:2])\n",
    "pages = list(range(1,l_e_int[-1]+1 ))\n",
    "print(pages)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 循环/遍历"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "# global varialbes \n",
    "html_raw = dict()\n",
    "main_content =\"\"\n",
    "element = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_pages (pages):\n",
    "    for p in pages:\n",
    "        print (p,end='\\t')\n",
    "\n",
    "        跳转_input = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/input')\n",
    "        跳转_a = driver.find_element_by_xpath('//span[@class=\"weui-desktop-pagination__form\"]/a')\n",
    "        跳转_input.clear()\n",
    "        跳转_input.send_keys(p)\n",
    "        跳转_a.click()\n",
    "\n",
    "        time.sleep(1)\n",
    "\n",
    "        element = driver.find_element_by_xpath('//div[@class=\"inner_link_article_list\"]')\n",
    "        main_content = element.get_attribute('innerHTML')\n",
    "        #print(main_content)\n",
    "        html_raw[p] = main_content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\t2\t3\t4\t5\t6\t7\t8\t9\t10\t11\t12\t13\t14\t15\t16\t17\t18\t19\t20\t21\t22\t23\t24\t25\t26\t27\t28\t29\t30\t31\t32\t33\t34\t35\t36\t37\t38\t39\t40\t41\t42\t43\t44\t45\t46\t47\t48\t49\t50\t51\t52\t53\t54\t55\t56\t57\t58\t59\t60\t61\t62\t63\t64\t65\t66\t67\t68\t69\t70\t71\t72\t73\t74\t75\t76\t77\t78\t79\t80\t81\t82\t83\t84\t85\t86\t87\t88\t89\t90\t91\t92\t93\t94\t95\t96\t97\t98\t99\t100\t101\t102\t103\t104\t105\t106\t107\t108\t109\t110\t111\t112\t113\t114\t115\t116\t117\t118\t119\t120\t121\t122\t123\t124\t125\t126\t127\t128\t129\t130\t131\t132\t133\t134\t135\t136\t137\t138\t139\t140\t141\t142\t143\t144\t145\t146\t147\t148\t149\t150\t151\t152\t153\t154\t155\t156\t157\t158\t159\t160\t161\t162\t163\t164\t165\t166\t167\t168\t169\t170\t171\t172\t173\t174\t175\t176\t177\t178\t179\t180\t181\t182\t183\t184\t185\t186\t187\t188\t189\t190\t191\t192\t193\t194\t195\t196\t197\t198\t199\t"
     ]
    }
   ],
   "source": [
    "process_pages(pages)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>html_snippets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>199 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         html_snippets\n",
       "1    <div><label class=\"inner_link_article_item\"><s...\n",
       "2    <div><label class=\"inner_link_article_item\"><s...\n",
       "3    <div><label class=\"inner_link_article_item\"><s...\n",
       "4    <div><label class=\"inner_link_article_item\"><s...\n",
       "5    <div><label class=\"inner_link_article_item\"><s...\n",
       "..                                                 ...\n",
       "195  <div><label class=\"inner_link_article_item\"><s...\n",
       "196  <div><label class=\"inner_link_article_item\"><s...\n",
       "197  <div><label class=\"inner_link_article_item\"><s...\n",
       "198  <div><label class=\"inner_link_article_item\"><s...\n",
       "199  <div><label class=\"inner_link_article_item\"><s...\n",
       "\n",
       "[199 rows x 1 columns]"
      ]
     },
     "execution_count": 228,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame([html_raw]).T\n",
    "df.columns = [\"html_snippets\"]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "32\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>html_snippets</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>&lt;div&gt;&lt;label class=\"inner_link_article_item\"&gt;&lt;s...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>167 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         html_snippets\n",
       "12   <div><label class=\"inner_link_article_item\"><s...\n",
       "34   <div><label class=\"inner_link_article_item\"><s...\n",
       "35   <div><label class=\"inner_link_article_item\"><s...\n",
       "36   <div><label class=\"inner_link_article_item\"><s...\n",
       "37   <div><label class=\"inner_link_article_item\"><s...\n",
       "..                                                 ...\n",
       "195  <div><label class=\"inner_link_article_item\"><s...\n",
       "196  <div><label class=\"inner_link_article_item\"><s...\n",
       "197  <div><label class=\"inner_link_article_item\"><s...\n",
       "198  <div><label class=\"inner_link_article_item\"><s...\n",
       "199  <div><label class=\"inner_link_article_item\"><s...\n",
       "\n",
       "[167 rows x 1 columns]"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_out = df[~df.duplicated()]\n",
    "print (len(df_out))\n",
    "df[df.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[12, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 199]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[12,\n",
       " 34,\n",
       " 35,\n",
       " 36,\n",
       " 37,\n",
       " 38,\n",
       " 39,\n",
       " 40,\n",
       " 41,\n",
       " 42,\n",
       " 43,\n",
       " 44,\n",
       " 45,\n",
       " 46,\n",
       " 47,\n",
       " 48,\n",
       " 49,\n",
       " 50,\n",
       " 51,\n",
       " 52,\n",
       " 53,\n",
       " 54,\n",
       " 55,\n",
       " 56,\n",
       " 57,\n",
       " 58,\n",
       " 59,\n",
       " 60,\n",
       " 61,\n",
       " 62,\n",
       " 63,\n",
       " 64,\n",
       " 65,\n",
       " 66,\n",
       " 67,\n",
       " 68,\n",
       " 69,\n",
       " 70,\n",
       " 71,\n",
       " 72,\n",
       " 73,\n",
       " 74,\n",
       " 75,\n",
       " 76,\n",
       " 77,\n",
       " 78,\n",
       " 79,\n",
       " 80,\n",
       " 81,\n",
       " 82,\n",
       " 83,\n",
       " 84,\n",
       " 85,\n",
       " 86,\n",
       " 87,\n",
       " 88,\n",
       " 89,\n",
       " 90,\n",
       " 91,\n",
       " 92,\n",
       " 93,\n",
       " 94,\n",
       " 95,\n",
       " 96,\n",
       " 97,\n",
       " 98,\n",
       " 99,\n",
       " 100,\n",
       " 101,\n",
       " 102,\n",
       " 103,\n",
       " 104,\n",
       " 105,\n",
       " 106,\n",
       " 107,\n",
       " 108,\n",
       " 109,\n",
       " 110,\n",
       " 111,\n",
       " 112,\n",
       " 113,\n",
       " 114,\n",
       " 115,\n",
       " 116,\n",
       " 117,\n",
       " 118,\n",
       " 119,\n",
       " 120,\n",
       " 121,\n",
       " 122,\n",
       " 123,\n",
       " 124,\n",
       " 125,\n",
       " 126,\n",
       " 127,\n",
       " 128,\n",
       " 129,\n",
       " 130,\n",
       " 131,\n",
       " 132,\n",
       " 133,\n",
       " 134,\n",
       " 135,\n",
       " 136,\n",
       " 137,\n",
       " 138,\n",
       " 139,\n",
       " 140,\n",
       " 141,\n",
       " 142,\n",
       " 143,\n",
       " 144,\n",
       " 145,\n",
       " 146,\n",
       " 147,\n",
       " 148,\n",
       " 149,\n",
       " 150,\n",
       " 151,\n",
       " 152,\n",
       " 153,\n",
       " 154,\n",
       " 155,\n",
       " 156,\n",
       " 157,\n",
       " 158,\n",
       " 159,\n",
       " 160,\n",
       " 161,\n",
       " 162,\n",
       " 163,\n",
       " 164,\n",
       " 165,\n",
       " 166,\n",
       " 167,\n",
       " 168,\n",
       " 169,\n",
       " 170,\n",
       " 171,\n",
       " 172,\n",
       " 173,\n",
       " 174,\n",
       " 175,\n",
       " 176,\n",
       " 177,\n",
       " 178,\n",
       " 179,\n",
       " 180,\n",
       " 181,\n",
       " 182,\n",
       " 183,\n",
       " 184,\n",
       " 185,\n",
       " 186,\n",
       " 187,\n",
       " 188,\n",
       " 189,\n",
       " 190,\n",
       " 191,\n",
       " 192,\n",
       " 193,\n",
       " 194,\n",
       " 195,\n",
       " 196,\n",
       " 197,\n",
       " 198,\n",
       " 199]"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "try_again = list(df[df.duplicated()].index)\n",
    "print(try_again)\n",
    "try_again = try_again + list (set(pages).difference(set(df.index.values)))\n",
    "try_again"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 暂存档"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "metadata": {},
   "outputs": [],
   "source": [
    "filename = fn [\"output\"] [\"中山大学南方学院_htm_snippets\"] \n",
    "df_out.to_csv(filename.format(公众号=\"中山大学南方学院\"), sep=\"\\t\", encoding=\"utf8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8,13,10,8,11,9,8,12,10,11,14,13,15,14,14,7,11,13,11,13,6,7,5,5,5,7,8,8,12,7,9,7,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,"
     ]
    }
   ],
   "source": [
    "def parse_html_snippets(_snippet_):\n",
    "    root = fromstring(_snippet_) \n",
    "    title = [x.text for x in root.xpath('//div[@class=\"inner_link_article_title\"]')]\n",
    "    create_time = [x.text for x in root.xpath('//div[@class=\"inner_link_article_date\"]')]\n",
    "    link = [x for x in root.xpath('//a/@href')]\n",
    "    _df_ = pd.DataFrame({\"title\":title, \"create_time\": create_time, \"link\":link})\n",
    "    return(_df_)\n",
    "    \n",
    "l_df = []\n",
    "for p in pages:\n",
    "    _df_ = parse_html_snippets(df.loc[p,\"html_snippets\"])\n",
    "    print (len(_df_), end=\",\")\n",
    "    l_df.append(_df_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>速看！南方学院学生返校告知书及返校指引来了！</td>\n",
       "      <td>2020-05-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>官宣！南方学院学生返校通知</td>\n",
       "      <td>2020-05-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>必看！南方学院教职员返校告知书及返校指引来了！</td>\n",
       "      <td>2020-05-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>官宣！南方学院教职员返校通知</td>\n",
       "      <td>2020-05-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>定了！南方学院学生返校预通知来了！</td>\n",
       "      <td>2020-05-16</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1809</th>\n",
       "      <td>第二轮“广东经济形势报告会”报告会暨南方论坛第111讲</td>\n",
       "      <td>2019-11-04</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1810</th>\n",
       "      <td>我校召开“不忘初心、牢记使命”主题教育对照党章党规找差距专题会议</td>\n",
       "      <td>2019-11-04</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1811</th>\n",
       "      <td>我校召开“不忘初心、牢记使命”主题教育思政工作调研座谈会</td>\n",
       "      <td>2019-11-04</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1812</th>\n",
       "      <td>南院13岁 | 萌娃有话要说</td>\n",
       "      <td>2019-10-31</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1813</th>\n",
       "      <td>幸“绘” | 走进南院，庆幸相“绘”</td>\n",
       "      <td>2019-10-30</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1814 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 title create_time  \\\n",
       "0               速看！南方学院学生返校告知书及返校指引来了！  2020-05-17   \n",
       "1                        官宣！南方学院学生返校通知  2020-05-17   \n",
       "2              必看！南方学院教职员返校告知书及返校指引来了！  2020-05-17   \n",
       "3                       官宣！南方学院教职员返校通知  2020-05-17   \n",
       "4                    定了！南方学院学生返校预通知来了！  2020-05-16   \n",
       "...                                ...         ...   \n",
       "1809       第二轮“广东经济形势报告会”报告会暨南方论坛第111讲  2019-11-04   \n",
       "1810  我校召开“不忘初心、牢记使命”主题教育对照党章党规找差距专题会议  2019-11-04   \n",
       "1811      我校召开“不忘初心、牢记使命”主题教育思政工作调研座谈会  2019-11-04   \n",
       "1812                    南院13岁 | 萌娃有话要说  2019-10-31   \n",
       "1813                幸“绘” | 走进南院，庆幸相“绘”  2019-10-30   \n",
       "\n",
       "                                                   link  \n",
       "0     http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "1     http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "2     http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "3     http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "4     http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "...                                                 ...  \n",
       "1809  http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "1810  http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "1811  http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "1812  http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "1813  http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "\n",
       "[1814 rows x 3 columns]"
      ]
     },
     "execution_count": 242,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_url_out = pd.concat(l_df).reset_index(drop=True)\n",
    "df_url_out.loc[0:1813]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "metadata": {},
   "outputs": [],
   "source": [
    "name=['title','create_time','link']\n",
    "test=pd.DataFrame(columns=name,data=df_url_out.loc[0:1813])\n",
    "with pd.ExcelWriter(fn[\"output\"][\"中山大学南方学院_xlsx\"].format(公众号=\"中山大学南方学院_requests\")) as writer:\n",
    "    test.to_excel(writer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1809</th>\n",
       "      <td>第二轮“广东经济形势报告会”报告会暨南方论坛第111讲</td>\n",
       "      <td>2019-11-04</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1810</th>\n",
       "      <td>我校召开“不忘初心、牢记使命”主题教育对照党章党规找差距专题会议</td>\n",
       "      <td>2019-11-04</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1811</th>\n",
       "      <td>我校召开“不忘初心、牢记使命”主题教育思政工作调研座谈会</td>\n",
       "      <td>2019-11-04</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1812</th>\n",
       "      <td>南院13岁 | 萌娃有话要说</td>\n",
       "      <td>2019-10-31</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1813</th>\n",
       "      <td>幸“绘” | 走进南院，庆幸相“绘”</td>\n",
       "      <td>2019-10-30</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 title create_time  \\\n",
       "1809       第二轮“广东经济形势报告会”报告会暨南方论坛第111讲  2019-11-04   \n",
       "1810  我校召开“不忘初心、牢记使命”主题教育对照党章党规找差距专题会议  2019-11-04   \n",
       "1811      我校召开“不忘初心、牢记使命”主题教育思政工作调研座谈会  2019-11-04   \n",
       "1812                    南院13岁 | 萌娃有话要说  2019-10-31   \n",
       "1813                幸“绘” | 走进南院，庆幸相“绘”  2019-10-30   \n",
       "\n",
       "                                                   link  \n",
       "1809  http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "1810  http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "1811  http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "1812  http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "1813  http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  "
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_url_out.tail(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>value</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>速看！南方学院学生返校告知书及返校指引来了！</td>\n",
       "      <td>2020-05-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>官宣！南方学院学生返校通知</td>\n",
       "      <td>2020-05-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>必看！南方学院教职员返校告知书及返校指引来了！</td>\n",
       "      <td>2020-05-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>官宣！南方学院教职员返校通知</td>\n",
       "      <td>2020-05-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>定了！南方学院学生返校预通知来了！</td>\n",
       "      <td>2020-05-16</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1804</th>\n",
       "      <td>幸“绘” | 走进南院，庆幸相“绘”</td>\n",
       "      <td>2019-10-30</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1807</th>\n",
       "      <td>糟糕！小人国的秘密基地要被发现了？</td>\n",
       "      <td>2019-11-05</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1809</th>\n",
       "      <td>第二轮“广东经济形势报告会”报告会暨南方论坛第111讲</td>\n",
       "      <td>2019-11-04</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1812</th>\n",
       "      <td>南院13岁 | 萌娃有话要说</td>\n",
       "      <td>2019-10-31</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1813</th>\n",
       "      <td>幸“绘” | 走进南院，庆幸相“绘”</td>\n",
       "      <td>2019-10-30</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>939 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             title create_time  \\\n",
       "value                                            \n",
       "0           速看！南方学院学生返校告知书及返校指引来了！  2020-05-17   \n",
       "1                    官宣！南方学院学生返校通知  2020-05-17   \n",
       "2          必看！南方学院教职员返校告知书及返校指引来了！  2020-05-17   \n",
       "3                   官宣！南方学院教职员返校通知  2020-05-17   \n",
       "4                定了！南方学院学生返校预通知来了！  2020-05-16   \n",
       "...                            ...         ...   \n",
       "1804            幸“绘” | 走进南院，庆幸相“绘”  2019-10-30   \n",
       "1807             糟糕！小人国的秘密基地要被发现了？  2019-11-05   \n",
       "1809   第二轮“广东经济形势报告会”报告会暨南方论坛第111讲  2019-11-04   \n",
       "1812                南院13岁 | 萌娃有话要说  2019-10-31   \n",
       "1813            幸“绘” | 走进南院，庆幸相“绘”  2019-10-30   \n",
       "\n",
       "                                                    link  \n",
       "value                                                     \n",
       "0      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "1      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "2      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "3      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "4      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "...                                                  ...  \n",
       "1804   http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "1807   http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "1809   http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "1812   http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "1813   http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...  \n",
       "\n",
       "[939 rows x 3 columns]"
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# tagging 标记\n",
    "tagging_list = [\"\",\"从化\",\"政府\",\"学校\",\"教育\",\"领导\"] #overwritable\n",
    "\n",
    "v_v_list = []\n",
    "\n",
    "for tag in tagging_list:\n",
    "    index_list = df_url_out [ df_url_out.title.str.contains(tag) ].index.tolist()\n",
    "    v_v_pairs = pd.DataFrame({tag:index_list}).melt().set_index(\"value\")\n",
    "    v_v_list.append(v_v_pairs)\n",
    "\n",
    "df_cat = v_v_list[0]\n",
    "for d in v_v_list:\n",
    "    df_cat.update(d)\n",
    "    \n",
    "# 尚未标记内容\n",
    "df_url_out.loc [ df_cat.query('variable==\"\"').index ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>url_cat</th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "      <th>variable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>209</th>\n",
       "      <td>广东省人民政府外事办公室致在粤外籍人士的一封公开信</td>\n",
       "      <td>2020-02-03</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>政府</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "url_cat                      title create_time  \\\n",
       "209      广东省人民政府外事办公室致在粤外籍人士的一封公开信  2020-02-03   \n",
       "\n",
       "url_cat                                               link variable  \n",
       "209      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...       政府  "
      ]
     },
     "execution_count": 183,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_o[df_o.title.str.contains(\"政府\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>url_cat</th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "      <th>variable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>速看！南方学院学生返校告知书及返校指引来了！</td>\n",
       "      <td>2020-05-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>官宣！南方学院学生返校通知</td>\n",
       "      <td>2020-05-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>定了！南方学院学生返校预通知来了！</td>\n",
       "      <td>2020-05-16</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>预告！“全国大学生同上一堂疫情防控思政大课”即将在线直播</td>\n",
       "      <td>2020-03-07</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>新青年 | 我校2018届毕业生杨坤入选《广东省大学生基层就业典型人物事迹》</td>\n",
       "      <td>2020-04-26</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>如何确保学生健康返校？家长请认真阅读这份指引！</td>\n",
       "      <td>2020-04-24</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>学生票最新政策：9月30日前都可以买！</td>\n",
       "      <td>2020-04-21</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>入境人员、留学生如何隔离？最新回应来了！</td>\n",
       "      <td>2020-03-21</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>请转扩！疫情期间寒假学生票延长至5月31日</td>\n",
       "      <td>2020-03-18</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>预告！“全国大学生同上一堂疫情防控思政大课”即将在线直播</td>\n",
       "      <td>2020-03-07</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>“隔空”部署，学生工作部召开2020届毕业生就业工作会议</td>\n",
       "      <td>2020-03-04</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>学生返校风险怎样防范？开学前学校应做好哪些应急预案？专业的建议来啦</td>\n",
       "      <td>2020-02-29</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>学校</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>我在战“疫”中成长！广东高校大学生抗疫征文活动开始啦</td>\n",
       "      <td>2020-02-27</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>从化区教育局致广大教育工作者、家长和学生的一封信</td>\n",
       "      <td>2020-02-07</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>从化</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>教育部致全国大学生的一封信</td>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\n",
       "      <td>我校2019年党建暨学生思想政治工作研讨会顺利召开</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>256</th>\n",
       "      <td>我校学生荣获第四届全省学生“学宪法、讲宪法”活动决赛演讲比赛高校组三等奖</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292</th>\n",
       "      <td>“习”语青年说——中山大学南方学院第一届学生微党课比赛</td>\n",
       "      <td>2019-11-27</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>293</th>\n",
       "      <td>沙场秋点“兵” ，青春正当时！ 2019级学生军训阅兵式暨总结表彰大会顺利举行</td>\n",
       "      <td>2019-11-26</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>298</th>\n",
       "      <td>第十四届广东大学生校园文体艺术节之第五届广东大学生定向越野赛复赛在中山大学南方学院顺利举行</td>\n",
       "      <td>2019-11-24</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "url_cat                                          title create_time  \\\n",
       "0                               速看！南方学院学生返校告知书及返校指引来了！  2020-05-17   \n",
       "1                                        官宣！南方学院学生返校通知  2020-05-17   \n",
       "4                                    定了！南方学院学生返校预通知来了！  2020-05-16   \n",
       "17                        预告！“全国大学生同上一堂疫情防控思政大课”即将在线直播  2020-03-07   \n",
       "35              新青年 | 我校2018届毕业生杨坤入选《广东省大学生基层就业典型人物事迹》  2020-04-26   \n",
       "38                             如何确保学生健康返校？家长请认真阅读这份指引！  2020-04-24   \n",
       "45                                 学生票最新政策：9月30日前都可以买！  2020-04-21   \n",
       "94                                入境人员、留学生如何隔离？最新回应来了！  2020-03-21   \n",
       "108                              请转扩！疫情期间寒假学生票延长至5月31日  2020-03-18   \n",
       "123                       预告！“全国大学生同上一堂疫情防控思政大课”即将在线直播  2020-03-07   \n",
       "133                       “隔空”部署，学生工作部召开2020届毕业生就业工作会议  2020-03-04   \n",
       "157                  学生返校风险怎样防范？开学前学校应做好哪些应急预案？专业的建议来啦  2020-02-29   \n",
       "162                         我在战“疫”中成长！广东高校大学生抗疫征文活动开始啦  2020-02-27   \n",
       "198                           从化区教育局致广大教育工作者、家长和学生的一封信  2020-02-07   \n",
       "206                                      教育部致全国大学生的一封信  2020-02-04   \n",
       "254                          我校2019年党建暨学生思想政治工作研讨会顺利召开  2020-01-04   \n",
       "256               我校学生荣获第四届全省学生“学宪法、讲宪法”活动决赛演讲比赛高校组三等奖  2020-01-03   \n",
       "292                        “习”语青年说——中山大学南方学院第一届学生微党课比赛  2019-11-27   \n",
       "293            沙场秋点“兵” ，青春正当时！ 2019级学生军训阅兵式暨总结表彰大会顺利举行  2019-11-26   \n",
       "298      第十四届广东大学生校园文体艺术节之第五届广东大学生定向越野赛复赛在中山大学南方学院顺利举行  2019-11-24   \n",
       "\n",
       "url_cat                                               link variable  \n",
       "0        http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "1        http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "4        http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "17       http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "35       http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "38       http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "45       http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "94       http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "108      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "123      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "133      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "157      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...       学校  \n",
       "162      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "198      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...       从化  \n",
       "206      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "254      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "256      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "292      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "293      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "298      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  "
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_o[df_o.title.str.contains(\"学生\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "name=['title','create_time','link']\n",
    "test=pd.DataFrame(columns=name,data=df_o[df_o.title.str.contains(\"学生\")])\n",
    "with pd.ExcelWriter(fn[\"output\"][\"中山大学南方学院_xlsx\"].format(公众号=\"中山大学南方学院_requests\")) as writer:\n",
    "    test.to_excel(writer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>url_cat</th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "      <th>variable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>关于开学复课你需要知道的6件事</td>\n",
       "      <td>2020-04-19</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>学生返校风险怎样防范？开学前学校应做好哪些应急预案？专业的建议来啦</td>\n",
       "      <td>2020-02-29</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>学校</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>快讯！广东各级各类学校2月底前不开学</td>\n",
       "      <td>2020-02-09</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>学校</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "url_cat                              title create_time  \\\n",
       "49                         关于开学复课你需要知道的6件事  2020-04-19   \n",
       "157      学生返校风险怎样防范？开学前学校应做好哪些应急预案？专业的建议来啦  2020-02-29   \n",
       "194                     快讯！广东各级各类学校2月底前不开学  2020-02-09   \n",
       "\n",
       "url_cat                                               link variable  \n",
       "49       http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "157      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...       学校  \n",
       "194      http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...       学校  "
      ]
     },
     "execution_count": 185,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_o[df_o.title.str.contains(\"开学\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>url_cat</th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "      <th>variable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>官宣！南方学院学生返校通知</td>\n",
       "      <td>2020-05-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>官宣！南方学院教职员返校通知</td>\n",
       "      <td>2020-05-17</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "url_cat           title create_time  \\\n",
       "1         官宣！南方学院学生返校通知  2020-05-17   \n",
       "3        官宣！南方学院教职员返校通知  2020-05-17   \n",
       "\n",
       "url_cat                                               link variable  \n",
       "1        http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "3        http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  "
      ]
     },
     "execution_count": 186,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_o[df_o.title.str.contains(\"官宣\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>url_cat</th>\n",
       "      <th>title</th>\n",
       "      <th>create_time</th>\n",
       "      <th>link</th>\n",
       "      <th>variable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>加快构建高校思想政治工作体系，教育部等八部门开始行动！</td>\n",
       "      <td>2020-05-13</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>看全文！教育部发布《高等学校新型冠状病毒肺炎防控指南》</td>\n",
       "      <td>2020-03-13</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>学校</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>毕业生福利！教育部联合“学习强国”上线24365校园招聘！</td>\n",
       "      <td>2020-03-10</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>我校参加全省教育系统全面从严治党视频会议</td>\n",
       "      <td>2020-04-24</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>战疫情，同上一堂国家安全教育课</td>\n",
       "      <td>2020-04-15</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1805</th>\n",
       "      <td>我校召开“不忘初心、牢记使命”主题教育校领导班子成员调研成果交流会</td>\n",
       "      <td>2019-11-06</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1806</th>\n",
       "      <td>攀藤附葛君须计 万载梅关著劫灰 ——机关党总支赴城口开展“不忘初心，牢记使命”主题教育活动</td>\n",
       "      <td>2019-11-06</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1808</th>\n",
       "      <td>主题教育进行时——我校组织观看中共十九届四中全会新闻发布会现场直播</td>\n",
       "      <td>2019-11-05</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1810</th>\n",
       "      <td>我校召开“不忘初心、牢记使命”主题教育对照党章党规找差距专题会议</td>\n",
       "      <td>2019-11-04</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1811</th>\n",
       "      <td>我校召开“不忘初心、牢记使命”主题教育思政工作调研座谈会</td>\n",
       "      <td>2019-11-04</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...</td>\n",
       "      <td>无法分类</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>860 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "url_cat                                          title create_time  \\\n",
       "7                          加快构建高校思想政治工作体系，教育部等八部门开始行动！  2020-05-13   \n",
       "9                          看全文！教育部发布《高等学校新型冠状病毒肺炎防控指南》  2020-03-13   \n",
       "13                       毕业生福利！教育部联合“学习强国”上线24365校园招聘！  2020-03-10   \n",
       "37                                我校参加全省教育系统全面从严治党视频会议  2020-04-24   \n",
       "52                                     战疫情，同上一堂国家安全教育课  2020-04-15   \n",
       "...                                                ...         ...   \n",
       "1805                 我校召开“不忘初心、牢记使命”主题教育校领导班子成员调研成果交流会  2019-11-06   \n",
       "1806     攀藤附葛君须计 万载梅关著劫灰 ——机关党总支赴城口开展“不忘初心，牢记使命”主题教育活动  2019-11-06   \n",
       "1808                 主题教育进行时——我校组织观看中共十九届四中全会新闻发布会现场直播  2019-11-05   \n",
       "1810                  我校召开“不忘初心、牢记使命”主题教育对照党章党规找差距专题会议  2019-11-04   \n",
       "1811                      我校召开“不忘初心、牢记使命”主题教育思政工作调研座谈会  2019-11-04   \n",
       "\n",
       "url_cat                                               link variable  \n",
       "7        http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "9        http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...       学校  \n",
       "13       http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "37       http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "52       http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "...                                                    ...      ...  \n",
       "1805     http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "1806     http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "1808     http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "1810     http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "1811     http://mp.weixin.qq.com/s?__biz=MzAxNTEwNjI1Ng...     无法分类  \n",
       "\n",
       "[860 rows x 4 columns]"
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_o[df_o.title.str.contains(\"教育\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>variable</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>无法分类</th>\n",
       "      <td>1796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>学校</th>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>从化</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>政府</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          title\n",
       "variable       \n",
       "无法分类       1796\n",
       "学校           14\n",
       "从化            3\n",
       "政府            1"
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_stats = df_o.groupby(by=\"variable\").agg({\"title\":\"count\"}).sort_values(by=\"title\", ascending=False)\n",
    "df_stats"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 输出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_account.columns.name = \"rel_accounts\"\n",
    "df_o.columns.name = \"url_cat\"\n",
    "df_stats.columns.name = \"stats\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {},
   "outputs": [],
   "source": [
    "_df_.columns.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the xlsxwriter workbook and worksheet objects.  \n",
    "with pd.ExcelWriter(fn[\"output\"][\"公众号_xlsx\"].format(公众号=公众号)) as writer:\n",
    "    workbook  = writer.book\n",
    "\n",
    "    for _df_ in [df_account, df_o, df_stats]:\n",
    "        _df_.to_excel(writer, sheet_name = _df_.columns.name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
